/*
 * EnigmaGM - A feature rich guild management system.
 * Copyright (C) 2007 ajiaojr.
 * 
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
 * 02110-1301, USA.
 */
package org.ajiaojr.enigmagm.db.mysql;

import org.ajiaojr.enigmagm.client.data.Character;
import org.ajiaojr.enigmagm.client.service.DataAccessException;
import org.ajiaojr.enigmagm.db.CharacterDAO;
import org.ajiaojr.enigmagm.db.ConnectionManager;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * @author ajiaojr
 */
public class MySQLCharacterDAOImpl implements CharacterDAO {
  private static final Log LOG = LogFactory.getLog(MySQLCharacterDAOImpl.class);

  public MySQLCharacterDAOImpl() {
  }

  public List findAttendees(int eventID, int firstResult, int maxResults)
      throws DataAccessException {
    Connection c = null;

    try {
      c = ConnectionManager.getInstance().getConnection();

      List result = new ArrayList();

      PreparedStatement s =
          c
              .prepareStatement("SELECT character_id, character_name, level, race_name, class_name, rank_name FROM attendance NATURAL JOIN character_list WHERE event_id = ? ORDER BY character_name LIMIT ?, ?");

      s.setInt(1, eventID);
      s.setInt(2, firstResult);
      s.setInt(3, maxResults);

      ResultSet r = s.executeQuery();

      while (r.next()) {
        result.add(new org.ajiaojr.enigmagm.client.data.Character(r.getInt(1),
            r.getString(2), r.getInt(3), r.getString(4), r.getString(5), r
                .getString(6)));
      }

      r.close();
      s.close();

      return result;
    } catch (Exception e) {
      throw new DataAccessException(e.getMessage());
    } finally {
      try {
        c.close();
      } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
    }
  }

  public Character findByCharacterID(int characterID)
      throws DataAccessException {
    Character result = null;

    Connection c = null;

    try {
      c = ConnectionManager.getInstance().getConnection();

      Statement s = c.createStatement();

      String sql =
          "SELECT character_name, level, race_name, class_name, rank_name FROM character_list WHERE character_id=\""
              + characterID + "\"";

      if (LOG.isDebugEnabled()) {
        LOG.debug("Finding user with query: " + sql);
      }

      ResultSet rs = s.executeQuery(sql);

      if (rs.next()) {
        result =
            new Character(characterID, rs.getString(1), rs.getInt(2), rs
                .getString(3), rs.getString(4), rs.getString(5));
      }

      rs.close();
      s.close();

    } catch (Exception e) {
      LOG.error("Error: ", e);
      throw new DataAccessException(e.getMessage());
    } finally {
      try {
        c.close();
      } catch (SQLException e) {
        LOG.error("Error: ", e);
      }
    }
    return result;
  }

  /*
   * (non-Javadoc)
   * 
   * @see org.ajiaojr.enigmagm.client.service.CharacterService#findByUser(java.lang.String,
   *      int, int)
   */
  public List findByUser(String username, int firstResult, int maxResults)
      throws DataAccessException {
    // TODO Auto-generated method stub
    return null;
  }

  /*
   * (non-Javadoc)
   * 
   * @see org.ajiaojr.enigmagm.client.service.CharacterService#list(java.lang.String,
   *      boolean, int, int)
   */
  public List list(String sortAttribute, boolean ascending, int firstResult,
      int maxResults) throws DataAccessException {
    Connection c = null;

    try {
      c = ConnectionManager.getInstance().getConnection();

      List result = new ArrayList();

      Statement s = c.createStatement();

      String sql =
          "SELECT character_id, character_name, level, race_name, class_name, rank_name, rank_id FROM character_list WHERE active=1 ORDER BY "
              + sortAttribute
              + " "
              + (ascending ? "ASC" : "DESC")
              + " LIMIT "
              + firstResult + ", " + maxResults;

      if (LOG.isDebugEnabled()) {
        LOG.debug("Listing users with query: " + sql);
      }

      ResultSet r = s.executeQuery(sql);

      while (r.next()) {
        result.add(new org.ajiaojr.enigmagm.client.data.Character(r.getInt(1),
            r.getString(2), r.getInt(3), r.getString(4), r.getString(5), r
                .getString(6)));
      }

      r.close();
      s.close();

      return result;
    } catch (Exception e) {
      throw new DataAccessException(e.getMessage());
    } finally {
      try {
        c.close();
      } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
    }
  }
}
